use mailserver
go
--lay ra tat ca roles
create proc getAllRoles
as
select * from Roles
go
-- xoa roles theo id
create proc sp_deleteRole
@id int
as
delete from Roles where Id=@id
go
--lay role theo id
create proc sp_getRoleByID
@id int
as
select * from Roles where Id=@id
go
-- them moi role
create proc sp_AddUpdateRoles
@id int,
@roleName varchar(50),
@isDisplay int
as
begin
	if(@id=0)
		insert into Roles(RoleName,IsDisplay) values(@roleName,@isDisplay)
	else
		update Roles set RoleName=@roleName, IsDisplay=@isDisplay where Id=@id
end
go
-- them moi Country
create proc sp_AddCountry
@name varchar(100)
as
insert into Country(CountryName) values(@name)

go
--lay tat ca country
create proc sp_getAllCountry
as
select * from Country
go
--xoa country theo id
create proc sp_deleteCountry
@id int
as
delete from Country where Id=@id
go
--sua country
create proc sp_editcountry
@id int,
@name varchar(100),
@is int
as
update Country set CountryName=@name,IsDisplay=@is where Id=@id
--lay tat ca city
go
create proc sp_getAllCity
as
select * from City

go
--add city
create proc sp_AddCity
@name varchar(100),
@countryID int,
@is int
as
insert into City(CityName,Country_Id,IsDispaly) values(@name,@countryID,@is)
go
--xoa city theo id
create proc sp_deleteCity
@id int
as
delete from City where Id=@id
go
--sua city theo id
create proc sp_editCity
@id int,
@name varchar(100),
@countryID int,
@isDis int
as
update City set CityName=@name, Country_Id=@countryID,IsDispaly=@isDis where Id=@id